﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace SQLDAL
{
    //测试partial，不使用时：会出现两个ContactGroup类
    //使用时：只会有一个ContactGroup类，但是这个类的内容，会包括Model命名空间中的属性、SQLDAL中的方法
    /// <summary>
    /// 数据访问类：ContactGroup
    /// </summary>
    public partial class ContactGroup
    {
        public ContactGroup() { }
        #region Method
        /// <summary>
        /// 根据查询条件获取分组列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetList(string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select id,groupname,memo from contactgroup");
            if (strWhere.Trim() != "")
            {
                sb.Append(" where " + strWhere);
            }
            return SqlDbHelper.ExecuteDataTable(sb.ToString());
        }
        /// <summary>
        /// 添加分组
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(Model.ContactGroup model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into contactgroup values(@groupname,@memo)");
            sb.Append(";select @@IDENTITY");
            SqlParameter[] parameters =
            {
                new SqlParameter("@groupname",SqlDbType.VarChar,50),
                new SqlParameter("@memo",SqlDbType.VarChar,200)
            };
            parameters[0].Value = model.GroupName;
            parameters[1].Value = model.Memo;
            int rows = SqlDbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, parameters);
            if(rows == 1)
            {
                return true;
            }
            else {
                return false;
            }
        }
        /// <summary>
        /// 根据分组编号，获取分组信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.ContactGroup GetModel(int id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from contactgroup where id = @id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",SqlDbType.Int)
            };
            parameters[0].Value = id;
            Model.ContactGroup model = new Model.ContactGroup();
            DataTable dt = SqlDbHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, parameters);
            if (dt.Rows.Count > 0)
            {
                model.Id = Convert.ToInt32(dt.Rows[0]["id"].ToString());
                model.GroupName = dt.Rows[0]["groupname"].ToString();
                if (dt.Rows[0]["memo"] != null && dt.Rows[0]["memo"].ToString() != "")
                    model.Memo = dt.Rows[0]["memo"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据分组姓名，获取分组信息
        /// </summary>
        /// <param name="groupName"></param>
        /// <returns></returns>
        public Model.ContactGroup GetModel(string groupName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from contactgroup");
            sb.Append(" where groupname = @groupname ");
            SqlParameter[] parameters =
            {
                new SqlParameter("@groupname",SqlDbType.VarChar,50)
            };
            parameters[0].Value = groupName;
            Model.ContactGroup model = new Model.ContactGroup();
            DataTable dt = SqlDbHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, parameters);
            if (dt.Rows.Count > 0)
            {
                model.Id = Convert.ToInt32(dt.Rows[0]["id"]);
                model.GroupName = dt.Rows[0]["groupname"].ToString();
                if (dt.Rows[0]["memo"] != null && dt.Rows[0]["memo"].ToString() != "")
                    model.Memo = dt.Rows[0]["memo"].ToString();
                return model;    
            }
            return null;
        }
        /// <summary>
        /// 删除分组
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public bool Delete(int Id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("delete from contactgroup");
            sb.Append(" where id = @id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",SqlDbType.Int)
            };
            parameters[0].Value = Id;
            int n = SqlDbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, parameters);
            if (n == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 根据分组ID，更新分组信息（未判断修改后的分组名称是否存在）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(Model.ContactGroup model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update contactgroup set ");
            sb.Append("groupname = @groupname,memo = @memo");
            sb.Append(" where id = @id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@groupname",SqlDbType.VarChar,50),
                new SqlParameter("@memo",SqlDbType.VarChar,200),
                new SqlParameter("@id",SqlDbType.Int)
            };
            parameters[0].Value = model.GroupName;
            parameters[1].Value = model.Memo;
            parameters[2].Value = model.Id;
            int rows = SqlDbHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, parameters);
            if (rows == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion Method
    }
}
